﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QuanLyKhachHang.Forms
{
    #region using
    using QuanLyKhachHang.BUS;
    using QuanLyKhachHang.DTO;
    using QuanLyKhachHang.Utilities;
    using System.IO;
    using NPOI.HSSF.UserModel;
    #endregion

    public partial class frmImportKHCoSD : Form
    {
        private delegate void TimeTask();
        private frmWaiting waiting;
        private DataSet objDonViDS;
        private DataTable dataTable = new DataTable();
        private string maDonVi;
        private int thang;
        private int nam;

        public frmImportKHCoSD()
        {
            InitializeComponent();
            InitGridViewHienThi();
        }

        private void WaitLoad()
        {
            waiting = new frmWaiting("Đang lấy dữ liệu");
            waiting.ShowDialog();
        }

        public void LoadDonVi()
        {
            var objBUS = new DonViBUS(GlobalVariables.g_strConnectionString);

            try
            {
                objDonViDS = objBUS.SelectAll();
            }
            catch (System.Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }
        }

        public void InitGridViewHienThi()
        {
            string str = "";
            long i = 0;
            DataColumn col1 = new DataColumn("STT", str.GetType());
            DataColumn col2 = new DataColumn("TENKH", str.GetType());
            DataColumn col3 = new DataColumn("LOAIHINH", str.GetType());

            DataColumn col4 = new DataColumn("NGANH", str.GetType());
            DataColumn col5 = new DataColumn("DIACHI", str.GetType());
            DataColumn col6 = new DataColumn("DICHVU", str.GetType());

            DataColumn col7 = new DataColumn("SL", i.GetType());
            DataColumn col8 = new DataColumn("DOANHTHU", i.GetType());
            DataColumn col9 = new DataColumn("THANG", str.GetType());
            DataColumn col10 = new DataColumn("NAM", str.GetType());
            dataTable.Columns.Add(col1);
            dataTable.Columns.Add(col2);
            dataTable.Columns.Add(col3);
            dataTable.Columns.Add(col4);
            dataTable.Columns.Add(col5);
            dataTable.Columns.Add(col6);
            dataTable.Columns.Add(col7);
            dataTable.Columns.Add(col8);
            dataTable.Columns.Add(col9);
            dataTable.Columns.Add(col10);
        }

        public bool ImportKhachHangCoSuDung(string path, ref string errorText)
        {
            errorText = "";
            try
            {
                dataTable.Clear();
                dtgvDanhSachHienThi.DataSource = null;
                dtgvDanhSachHienThi.Columns.Clear();

                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    var dt = new DataTable("KhachHang");
                   
                    dt.Columns.Add("STT");
                    dt.Columns.Add("MADONVI");
                    dt.Columns.Add("THANG");
                    dt.Columns.Add("NAM");
                    dt.Columns.Add("TENKH");
                    dt.Columns.Add("LOAIHINH");

                    //dich vu
                    dt.Columns.Add("NGANH");
                    dt.Columns.Add("DIACHI");
                    dt.Columns.Add("DIENTHOAI");
                    dt.Columns.Add("FAX");
                    dt.Columns.Add("NGAYKINIEM");
                    dt.Columns.Add("TENNV");
                    dt.Columns.Add("NGAYSINHNV");
                    dt.Columns.Add("DIENTHOAINV");
                    dt.Columns.Add("EMAILNV");
                    dt.Columns.Add("DICHVU");
                    dt.Columns.Add("SL");
                    dt.Columns.Add("DOANHTHU");
                    dt.Columns.Add("TGBATDAU");

                    int indexRow = 0;
                    while (rows.MoveNext())
                    {
                        HSSFRow row = (HSSFRow)rows.Current;
                        switch (indexRow)
                        {
                            case 1:
                                //get donvi infos
                                string[] compdonvi = row.Cells[0].StringCellValue.Trim().Split(new char[] { ':' });
                                var tendonvi = compdonvi[1].Trim();
                                lblDonVi.Text = tendonvi;
                                maDonVi = Utils.GetMaDonVi(objDonViDS.Tables[0], tendonvi);
                                break;

                            case 3:
                            case 4:
                                if (row.Cells[0].ToString() != "")
                                {
                                    if (row.Cells[0].StringCellValue.Trim().ToLower().Contains("tháng") &&
                                        row.Cells[0].StringCellValue.Trim().ToLower().Contains("năm"))
                                    {
                                        //get month, year infos
                                        if (!Utils.ValidateNgayThang(row.Cells[0].StringCellValue, ref thang, ref nam, ref errorText))
                                            return false;
                                        lblThang.Text = thang.ToString();
                                        lblNam.Text = nam.ToString();
                                    }
                                }
                                break;

                            default:
                                if (Utils.IsNumeric(row.Cells[0].ToString()))
                                {
                                    dt.Rows.Add(row.Cells[0].NumericCellValue, 
                                                maDonVi, 
                                                thang,
                                                nam,
                                                row.Cells[1].StringCellValue,
                                                row.Cells[2].StringCellValue,
                                                row.Cells[3].StringCellValue,
                                                row.Cells[4].StringCellValue,
                                                row.Cells[5].NumericCellValue.ToString(),
                                                row.Cells[6].NumericCellValue.ToString(),
                                                row.Cells[7].StringCellValue,
                                                row.Cells[8].StringCellValue,
                                                row.Cells[9].StringCellValue,
                                                row.Cells[10].StringCellValue,
                                                row.Cells[11].StringCellValue,
                                                row.Cells[12].StringCellValue,
                                                row.Cells[13].NumericCellValue,
                                                row.Cells[14].NumericCellValue,
                                                row.Cells[15].StringCellValue);
                                }
                                break;
                        }
                        indexRow++;
                    }

                    int count = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow row = dt.Rows[i];

                        KH_CO_SD_TIEMNANGDTO khmoi = new KH_CO_SD_TIEMNANGDTO
                        {
                            STT = row["STT"].ToString(),
                            MADONVI = row["MADONVI"].ToString(),
                            THANG = int.Parse(row["THANG"].ToString()),
                            NAM = int.Parse(row["NAM"].ToString()),
                            TENKH = row["TENKH"].ToString(),
                            LOAIHINH = row["LOAIHINH"].ToString(),
                            NGANH = row["NGANH"].ToString(),
                            DIACHI = row["DIACHI"].ToString(),
                            DIENTHOAI = row["DIENTHOAI"].ToString(),
                            FAX = row["FAX"].ToString(),
                            NGAYKINIEM = row["NGAYKINIEM"].ToString(),
                            TENNV = row["TENNV"].ToString(),
                            NGAYSINHNV = row["NGAYSINHNV"].ToString(),
                            DIENTHOAINV = row["DIENTHOAINV"].ToString(),
                            EMAILNV = row["EMAILNV"].ToString(),
                            DICHVU = row["DICHVU"].ToString(),
                            SL = int.Parse(row["SL"].ToString()),
                            DOANHTHU = long.Parse(row["DOANHTHU"].ToString()),
                        };

                        var khcsd = new KH_CO_SDDTO
                        {
                            TGBATDAU = row["TGBATDAU"].ToString()
                        };
                        var objBUS = new KH_CO_SDBUS(GlobalVariables.g_strConnectionString);
                        if (objBUS.Insert(khmoi, khcsd))
                        {
                            DataRow row2;
                            dataTable.Rows.Add();
                            row2 = dataTable.Rows[count];

                            row2["STT"] = khmoi.STT;
                            row2["TENKH"] = khmoi.TENKH;
                            row2["LOAIHINH"] = khmoi.LOAIHINH;
                            row2["NGANH"] = khmoi.NGANH;
                            row2["DIACHI"] = khmoi.DIACHI;
                            row2["DICHVU"] = khmoi.DICHVU;
                            row2["SL"] = khmoi.SL;
                            row2["DOANHTHU"] = khmoi.DOANHTHU;
                            row2["THANG"] = khmoi.THANG;
                            row2["NAM"] = khmoi.NAM;
                        }

                        count++;
                    }
                   
                    DataView objTimeSheetDV = new System.Data.DataView(dataTable);
                    objTimeSheetDV.AllowNew = false;
                    dtgvDanhSachHienThi.AutoGenerateColumns = false;
                    dtgvDanhSachHienThi.DataSource = objTimeSheetDV;

                    DataGridViewTextBoxColumn objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "STT",
                        DataPropertyName = "STT",
                        Name = "STT",
                        Width = 30
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Tên KH",
                        DataPropertyName = "TenKH",
                        Name = "TenKH",
                        Width = 150
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Loại hình",
                        DataPropertyName = "LoaiHinh",
                        Name = "LoaiHinh",
                        Width = 60
                    };

                    dtgvDanhSachHienThi.Columns.Add(objColumn);


                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Ngành nghề KD Chính",
                        DataPropertyName = "Nganh",
                        Name = "Nganh",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Địa chỉ",
                        DataPropertyName = "DiaChi",
                        Name = "DiaChi",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Dịch vụ",
                        DataPropertyName = "DichVu",
                        Name = "DichVu",
                        Width = 120
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Sản lượng",
                        DataPropertyName = "SL",
                        Name = "SL",
                        Width = 60
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        HeaderText = "Doanh thu",
                        DataPropertyName = "DoanhThu",
                        Name = "DoanhThu",
                        Width = 60
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "THANG",
                        Name = "THANG",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);

                    objColumn = new DataGridViewTextBoxColumn
                    {
                        DataPropertyName = "NAM",
                        Name = "NAM",
                        Visible = false
                    };
                    dtgvDanhSachHienThi.Columns.Add(objColumn);
                }

                dtgvDanhSachHienThi.ClearSelection();
                return true;
            }
            catch
            {
                return false;
            }
        }

        private void btnChonFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog frm = new OpenFileDialog();

            frm.Title = "Lua chon file import";
            frm.Filter = "Executable file (*.xls)|*.xls";
            frm.RestoreDirectory = true;
            frm.InitialDirectory = Application.StartupPath + "\\Imports";

            if (frm.ShowDialog() == DialogResult.OK)
            {
                string errorText = "";
                var ASynInvoke = new TimeTask(WaitLoad);
                IAsyncResult result = ASynInvoke.BeginInvoke(null, null);

                GlobalVariables.g_strPathKHLon = frm.FileName;
                lblTenDuongDan.Text = frm.FileName;
                //import du lieu khach hang lon
                var isvalid = ImportKhachHangCoSuDung(GlobalVariables.g_strPathKHLon, ref errorText);
                
                if (!isvalid)
                {
                    waiting.Hide();
                    MessageBox.Show(errorText, "Bao Loi",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                waiting.Close();
                waiting.Dispose();
                waiting = null;
                ASynInvoke.EndInvoke(result);
            }
        }

        private void frmImportKHCoSD_Load(object sender, EventArgs e)
        {
            LoadDonVi();
        }

        private void dtgvDanhSachHienThi_Click(object sender, EventArgs e)
        {
            try
            {
                string stt = dtgvDanhSachHienThi.SelectedRows[0].Cells[0].Value.ToString();
                var objBUS = new KH_CO_SDBUS(GlobalVariables.g_strConnectionString);
                thang = int.Parse(dtgvDanhSachHienThi.SelectedRows[0].Cells["THANG"].Value.ToString());
                nam = int.Parse(dtgvDanhSachHienThi.SelectedRows[0].Cells["NAM"].Value.ToString());
                var objDS = objBUS.SelectByID(stt, thang, nam);
                if (objDS.Tables[0].Rows.Count > 0)
                {
                    var row = objDS.Tables[0].Rows[0];
                    txtTenKH.Text = row["TENKH"].ToString();
                    txtLoaiHinh.Text = row["LOAIHINH"].ToString();
                    txtFax.Text = row["FAX"].ToString();
                    txtTenNV.Text = row["TENNV"].ToString();
                    txtDichVu.Text = row["DICHVU"].ToString();
                    txtTGBatDau.Text = row["TGBATDAU"].ToString();
                    txtDiaChi.Text = row["DIACHI"].ToString();
                    txtNganh.Text = row["NGANH"].ToString();
                    txtDienThoai.Text = row["DIENTHOAI"].ToString();
                    txtNgayKN.Text = row["NGAYKINIEM"].ToString();
                    txtEmailNV.Text = row["EMAILNV"].ToString();
                    txtNgaySinhNV.Text = row["NGAYSINHNV"].ToString();
                    txtDienThoaiNV.Text = row["DIENTHOAINV"].ToString();
                    txtSoLuong.Text = row["SL"].ToString();
                    txtDoanhThu.Text = row["DOANHTHU"].ToString();
                }
            }
            catch
            {
            }
        }

    }
}
